package com.yjck.fm.spend.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.yjck.fm.common.QueryResult;
import com.yjck.fm.util.SQLUtil;
import com.yjck.fm.spend.bean.SpendType;
import com.yjck.fm.spend.dao.SpendManagerDAO;
import com.yjck.fm.spend.dao.handler.SpendInfoRowMapper;
import com.yjck.fm.spend.dao.handler.SpendTypeRowMapper;

public class SpendManagerDAOImpl implements SpendManagerDAO {
	private JdbcTemplate jdbcTemplate;

	@SuppressWarnings("unchecked")
	public List<SpendType> getSpendTypeList() {
		String sql = "select id,spendname,'' uname,'' createdate from spendtype";
		List<SpendType> list = this.jdbcTemplate.query(sql,
				new SpendTypeRowMapper());
		return list;
	}

	public boolean addSpendInfo(String sdesc, String sdate, String price, String type,
			Integer uid) {
		String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		String sql = "insert into spendinfo (spendtypeid,spenddesc,price,spenddate,createby,createdate,updateby,updatedate) values (?,?,?,?,?,?,?,?)";
		Object[] params = new Object[]{type,sdesc,price,sdate,uid,date,uid,date};
		int flag = this.jdbcTemplate.update(sql, params);
		if(flag > 0){
			return true;
		}else{
			return false;
		}
	}

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public QueryResult getSpendInfos(String sdesc, String type, String sdate,int start,int limit) {
		StringBuffer querySQL = new StringBuffer();
			querySQL.append("SELECT info.id,info.spenddesc,info.spenddate,type.spendname,info.price,info.spendtypeid ")
					.append("from spendinfo info,spendtype type ")
					.append("where info.spendtypeid = type.id ")
					.append("and info.spenddesc like '%"+sdesc+"%' ")
					.append("and info.spendtypeid like '%" +type+"%' " )
					.append("and info.spenddate like '%" + sdate +"%' ")
					.append("limit "+(limit+start)+" offset "+start);
		List list =  this.jdbcTemplate.query(querySQL.toString(), new SpendInfoRowMapper());
		int count = this.jdbcTemplate.queryForInt(SQLUtil.getCountSQL(querySQL.toString()));
		return new QueryResult(count, list);
	}

}
